#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
Python 3.x
SQLAlchemy 1.4.41

SQLAlchemy 高级查询
"""

from sqlalchemy import Column, Integer, String, Text
from constants import DB_URI
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(DB_URI, echo=True)

# 所有的类都要继承自`declarative_base`这个函数生成的基类
Base = declarative_base(engine)


class Menu(Base):
    __tablename__ = "menu"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)

    def __repr__(self):
        return "<Article(id:%s, name:%s)>" % (self.id, self.name)


# 懒加载
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(50), nullable=False)
    author_id = Column(Integer, ForeignKey('author.id'))
    authors = relationship('Author', back_populates="addresses")

    def __repr__(self):
        return "<Address(id='%s', email_address='%s', author_id='%s')>" % (self.id, self.email_address, self.author_id)


class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    addresses = relationship("Address", order_by=Address.id, back_populates="authors", lazy='dynamic')

    def __repr__(self):
        return "<Author(id='%s', username='%s')>" % (self.id, self.username)


# 映射到数据库
Base.metadata.drop_all()
Base.metadata.create_all()

Session = sessionmaker(bind=engine)
session = Session()

menus = [
    Menu(name="level 1"),
    Menu(name="level 2"),
    Menu(name="level 3"),
    Menu(name="level 4"),
    Menu(name="level 5"),
    Menu(name="level 5")
]
session.add_all(menus)  # 批量增加
session.commit()

tmp_menus = session.query(Menu).all()
print(tmp_menus)

"""
排序
"""
# 正向排序
tmp_menus = session.query(Menu).order_by(Menu.id).all()
print(tmp_menus)
# > [<Article(id:1, name:level 1)>, <Article(id:2, name:level 2)>, <Article(id:3, name:level 3)>,
# >  <Article(id:4, name:level 4)>, <Article(id:5, name:level 5)>]
# 反向排序
tmp_menus = session.query(Menu).order_by(-Menu.id).all()
print(tmp_menus)
# > [<Article(id:5, name:level 5)>, <Article(id:4, name:level 4)>, <Article(id:3, name:level 3)>,
# >  <Article(id:2, name:level 2)>, <Article(id:1, name:level 1)>]
tmp_menus = session.query(Menu).order_by(Menu.id.desc()).all()
print(tmp_menus)
# > [<Article(id:5, name:level 5)>, <Article(id:4, name:level 4)>, <Article(id:3, name:level 3)>,
# >  <Article(id:2, name:level 2)>, <Article(id:1, name:level 1)>]

"""
limit，offset, slice, 切片
"""
# limit
tmp_menus = session.query(Menu).limit(2).all()
print(tmp_menus)
# > [<Article(id:1, name:level 1)>, <Article(id:2, name:level 2)>]

# offset
tmp_menus = session.query(Menu).offset(2).all()
print(tmp_menus)
# > [<Article(id:3, name:level 3)>, <Article(id:4, name:level 4)>, <Article(id:5, name:level 5)>]

# slice
tmp_menus = session.query(Menu).slice(2, 4).all()
print(tmp_menus)
# > [<Article(id:3, name:level 3)>, <Article(id:4, name:level 4)>]

# 切片
tmp_menus = session.query(Menu)[2: 4]
print(tmp_menus)
# > [<Article(id:3, name:level 3)>, <Article(id:4, name:level 4)>]

"""
高级查询
"""
from sqlalchemy import func

# `group_by`
tmp_menus = session.query(Menu.name, func.count(Menu.id)).group_by(Menu.name).all()
print(tmp_menus)
# > [('level 1', 1), ('level 2', 1), ('level 3', 1), ('level 4', 1), ('level 5', 2)]

# `having`
tmp_menus = session.query(Menu.name, func.count(Menu.id)).group_by(Menu.name).having(Menu.name == 'level 5').all()
print(tmp_menus)
# > [('level 5', 2)]

author = Author(username="YiYeBiJi")
author.addresses = [
    Address(email_address='YiYeBiJi@126.com'),
    Address(email_address='YiYeBiJi@163.com')
]
session.add(author)
session.commit()

# `join`
tmp_res = session.query(Author, Address).filter(Author.id == Address.author_id).all()
for author, address in tmp_res:
    print(author)
    print(address)
# > <Author(id='1', username='YiYeBiJi')>
# > <Address(id='1', email_address='YiYeBiJi@126.com', author_id='1')>
# > <Author(id='1', username='YiYeBiJi')>
# > <Address(id='2', email_address='YiYeBiJi@163.com', author_id='1')>

tmp_res = session.query(Author, Address).join(Address).all()
for author, address in tmp_res:
    print(author)
    print(address)
# > <Author(id='1', username='YiYeBiJi')>
# > <Address(id='1', email_address='YiYeBiJi@126.com', author_id='1')>
# > <Author(id='1', username='YiYeBiJi')>
# > <Address(id='2', email_address='YiYeBiJi@163.com', author_id='1')>

author = Author(username="YiYeBiJi02")
session.add(author)
session.commit()
tmp_res = session.query(Author, Address).outerjoin(Address).all()
for res in tmp_res:
    print(res)
# > (<Author(id='1', username='YiYeBiJi')>, <Address(id='2', email_address='YiYeBiJi@163.com', author_id='1')>)
# > (<Author(id='1', username='YiYeBiJi')>, <Address(id='1', email_address='YiYeBiJi@126.com', author_id='1')>)
# > (<Author(id='2', username='YiYeBiJi02')>, None)

address = Address(email_address='YiYeBiJi02@163.com')
session.add(address)
session.commit()
tmp_res = session.query(Author, Address).outerjoin(Author).all()
for res in tmp_res:
    print(res)
# > (<Author(id='1', username='YiYeBiJi')>, <Address(id='1', email_address='YiYeBiJi@126.com', author_id='1')>)
# > (<Author(id='1', username='YiYeBiJi')>, <Address(id='2', email_address='YiYeBiJi@163.com', author_id='1')>)
# > (None, <Address(id='3', email_address='YiYeBiJi02@163.com', author_id='None')>)

# `别名`
from sqlalchemy.orm import aliased

adalias1 = aliased(Address)
adalias2 = aliased(Address)
tmp_res = session.query(Author.username, adalias1.email_address, adalias2.email_address).join(adalias1).join(
    adalias2).all()
for username, email1, email2 in tmp_res:
    print(username, email1, email2)
# > YiYeBiJi YiYeBiJi@126.com YiYeBiJi@126.com
# > YiYeBiJi YiYeBiJi@163.com YiYeBiJi@126.com
# > YiYeBiJi YiYeBiJi@126.com YiYeBiJi@163.com
# > YiYeBiJi YiYeBiJi@163.com YiYeBiJi@163.com

# `子查询`
from sqlalchemy.sql import func

# 构造子查询
stmt = session.query(Address.author_id.label('author_id'), func.count("*").label('address_count')).group_by(
    Address.author_id).subquery()

# 将子查询放到父查询中
tmp_res = session.query(Author, stmt.c.address_count).outerjoin(stmt, Author.id == stmt.c.author_id).order_by(Author.id)
for u, count in tmp_res:
    print(u, count)
# > <Author(id='1', username='YiYeBiJi')> 2
# > <Author(id='2', username='YiYeBiJi02')> None

stmt = session.query(Address).subquery()
adalias = aliased(Address, stmt)
tmp_res = session.query(Author, adalias).join(stmt, Author.addresses)
for res in tmp_res:
    print(res)
# > (<Author(id='1', username='YiYeBiJi')>, <Address(id='1', email_address='YiYeBiJi@126.com', author_id='1')>)
# > (<Author(id='1', username='YiYeBiJi')>, <Address(id='2', email_address='YiYeBiJi@163.com', author_id='1')>)
